<html>
<!-- =====================================================================

  File:      usp_OrdersAdd.htm for Adventure Works Cycles Storefront Sample
  Summary:   Self-documentation for application
  Date:	     June 16, 2003

=====================================================================

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= -->
    <head>
        <title>Adventure Works Cycles Store Documentation</title>
        <link rel="stylesheet" href="style.css">
    </head>
    <body class="NormalIndent">
        <h1>
            "usp_OrdersAdd" Stored Procedure
        </h1>
        <b>Description:</b>
        <P>
            This stored procedure first creates a record in the SalesOrderHeader table for the order
            being created.&nbsp; Then, it adds all of the products from the user's shopping
            cart into the SalesOrderDetail table, and then empties the user's shopping
            cart.&nbsp; It performs all three of these steps in a transaction.&nbsp; This
            ensures that if an error occurs adding an item to the SalesOrderDetail table, there
            will not be an empty order left in the SalesOrderHeader table.&nbsp;
        </P>
        <b>Definition:</b>
<pre style="background-color:white">
<span style="color: #0000FF"><strong>CREATE</strong></span> Procedure usp_OrdersAdd
<span style="color: #4444FF">(</span>
    @CustomerID 	<span style="color: #2040a0"><strong>int</strong></span><span style="color: #4444FF">,</span>
    @CartID     	<span style="color: #2040a0"><strong>nvarchar</strong></span><span style="color: #4444FF">(</span>50<span style="color: #4444FF">)</span><span style="color: #4444FF">,</span>
    @OrderDate  	<span style="color: #2040a0"><strong>datetime</strong></span><span style="color: #4444FF">,</span>        
    @ShipDate   	<span style="color: #2040a0"><strong>datetime</strong></span><span style="color: #4444FF">,</span>
	@ShippingMethod	<span style="color: #2040a0"><strong>int</strong></span><span style="color: #4444FF">,</span>
	@Status			<span style="color: #2040a0"><strong>int</strong></span><span style="color: #4444FF">,</span>
	@BillToAddressID	<span style="color: #2040a0"><strong>int</strong></span><span style="color: #4444FF">,</span>
	@ShipToAddressID	<span style="color: #2040a0"><strong>int</strong></span><span style="color: #4444FF">,</span>
    @OrderID    	<span style="color: #2040a0"><strong>int</strong></span> OUTPUT
<span style="color: #4444FF">)</span>
WITH EXECUTE <span style="color: #0000FF"><strong>AS</strong></span> OWNER
<span style="color: #0000FF"><strong>AS</strong></span>

<span style="color: #0000FF"><strong>BEGIN</strong></span> <span style="color: #0000FF"><strong>TRANSACTION</strong></span> AddOrder;
DECLARE @err <span style="color: #2040a0"><strong>int</strong></span>
DECLARE @rc <span style="color: #2040a0"><strong>int</strong></span>
DECLARE @ContactID <span style="color: #2040a0"><strong>int</strong></span>

DECLARE @OrderTotal money
<span style="color: #0000FF"><strong>SELECT</strong></span> 
       @OrderTotal <span style="color: #4444FF">=</span> Cast<span style="color: #4444FF">(</span>sum<span style="color: #4444FF">(</span>SCI.Quantity<span style="color: #4444FF">*</span>P.ListPrice<span style="color: #4444FF">)</span> <span style="color: #0000FF"><strong>as</strong></span> money<span style="color: #4444FF">)</span> 
<span style="color: #0000FF"><strong>FROM</strong></span> 
     Sales.ShoppingCartItem <span style="color: #0000FF"><strong>as</strong></span> SCI
LEFT OUTER JOIN
	Production.Product <span style="color: #0000FF"><strong>as</strong></span> P <span style="color: #0000FF"><strong>ON</strong></span> SCI.ProductID <span style="color: #4444FF">=</span> P.ProductID
<span style="color: #0000FF"><strong>WHERE</strong></span>
	SCI.ShoppingCartID <span style="color: #4444FF">=</span> @CartID;

<span style="color: #0000FF"><strong>SELECT</strong></span> @err <span style="color: #4444FF">=</span> @@error;
IF <span style="color: #4444FF">(</span>@err <span style="color: #4444FF">&lt;</span><span style="color: #4444FF">&gt;</span> 0<span style="color: #4444FF">)</span> GOTO <span style="color: #0000FF"><strong>abort</strong></span>

<span style="color: #0000FF"><strong>SELECT</strong></span>
	@ContactID <span style="color: #4444FF">=</span> ContactID
<span style="color: #0000FF"><strong>FROM</strong></span> 
	Sales.Individual
<span style="color: #0000FF"><strong>WHERE</strong></span> 
	CustomerID <span style="color: #4444FF">=</span> @CustomerID;

<span style="color: #0000FF"><strong>SELECT</strong></span> @err <span style="color: #4444FF">=</span> @@error<span style="color: #4444FF">,</span>
	@rc <span style="color: #4444FF">=</span> @@rowcount;

if <span style="color: #4444FF">(</span>@err <span style="color: #4444FF">&lt;</span><span style="color: #4444FF">&gt;</span> 0 <span style="color: #0000FF"><strong>OR</strong></span> @rc <span style="color: #4444FF">&lt;</span> 1<span style="color: #4444FF">)</span> GOTO <span style="color: #0000FF"><strong>abort</strong></span>
	
<span style="color: #008000">-- TODO: Come up with a better tax and freight story in a future release.</span>

<span style="color: #008000">-- Create the Order header </span>
<span style="color: #0000FF"><strong>INSERT</strong></span> <span style="color: #0000FF"><strong>INTO</strong></span> Sales.SalesOrderHeader
<span style="color: #4444FF">(</span>
    CustomerID<span style="color: #4444FF">,</span> 
    SubTotal<span style="color: #4444FF">,</span>
    TaxAmt<span style="color: #4444FF">,</span>
    Freight<span style="color: #4444FF">,</span>
    OrderDate<span style="color: #4444FF">,</span>
	DueDate<span style="color: #4444FF">,</span> 
    ShipDate<span style="color: #4444FF">,</span>
	ShipMethodID<span style="color: #4444FF">,</span>
	ContactID<span style="color: #4444FF">,</span>
    OnlineOrderFlag<span style="color: #4444FF">,</span>
	Status<span style="color: #4444FF">,</span>
	BillToAddressID<span style="color: #4444FF">,</span>
	ShipToAddressID

<span style="color: #4444FF">)</span>
VALUES
<span style="color: #4444FF">(</span>   
    @CustomerID<span style="color: #4444FF">,</span> 
    @OrderTotal<span style="color: #4444FF">,</span>
    0<span style="color: #4444FF">,</span>
    cast <span style="color: #4444FF">(</span>@OrderTotal <span style="color: #4444FF">*</span> .1 <span style="color: #0000FF"><strong>AS</strong></span> money<span style="color: #4444FF">)</span><span style="color: #4444FF">,</span>
    @OrderDate<span style="color: #4444FF">,</span> 
    @ShipDate<span style="color: #4444FF">,</span>
	@ShipDate<span style="color: #4444FF">,</span>
	@ShippingMethod<span style="color: #4444FF">,</span>
	@ContactID<span style="color: #4444FF">,</span>
    1<span style="color: #4444FF">,</span>
	@Status<span style="color: #4444FF">,</span>
	@BillToAddressID<span style="color: #4444FF">,</span>
	@ShipToAddressID
<span style="color: #4444FF">)</span>;

<span style="color: #0000FF"><strong>SELECT</strong></span>
    @OrderID <span style="color: #4444FF">=</span> SCOPE_IDENTITY<span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">,</span>
    @err <span style="color: #4444FF">=</span> @@error;

IF <span style="color: #4444FF">(</span>@err <span style="color: #4444FF">&lt;</span><span style="color: #4444FF">&gt;</span> 0<span style="color: #4444FF">)</span> GOTO <span style="color: #0000FF"><strong>abort</strong></span>



<span style="color: #008000">-- Copy items from given shopping cart to SalesOrderDetail table for given OrderID</span>

<span style="color: #0000FF"><strong>INSERT</strong></span> <span style="color: #0000FF"><strong>INTO</strong></span> Sales.SalesOrderDetail
      <span style="color: #4444FF">(</span>
	SalesOrderID<span style="color: #4444FF">,</span> 
	ProductID<span style="color: #4444FF">,</span> 
	SpecialOfferID<span style="color: #4444FF">,</span>
	OrderQty<span style="color: #4444FF">,</span> 
	UnitPrice
      <span style="color: #4444FF">)</span>
	<span style="color: #4444FF">(</span><span style="color: #0000FF"><strong>SELECT</strong></span> @OrderID<span style="color: #4444FF">,</span> 
	       SCI.ProductID<span style="color: #4444FF">,</span> 
		   1<span style="color: #4444FF">,</span> <span style="color: #008000">-- No discount</span>
		   SCI.Quantity<span style="color: #4444FF">,</span> 
		   P.ListPrice
	<span style="color: #0000FF"><strong>FROM</strong></span> 
	     Sales.ShoppingCartItem <span style="color: #0000FF"><strong>as</strong></span> SCI
	INNER JOIN Production.Product <span style="color: #0000FF"><strong>as</strong></span> P <span style="color: #0000FF"><strong>ON</strong></span> SCI.ProductID <span style="color: #4444FF">=</span> P.ProductID
	<span style="color: #0000FF"><strong>WHERE</strong></span> 
	      SCI.ShoppingCartID <span style="color: #4444FF">=</span> @CartID<span style="color: #4444FF">)</span>

       <span style="color: #0000FF"><strong>SELECT</strong></span> @err <span style="color: #4444FF">=</span> @@error;
       IF <span style="color: #4444FF">(</span>@err <span style="color: #4444FF">&lt;</span><span style="color: #4444FF">&gt;</span> 0<span style="color: #4444FF">)</span> GOTO <span style="color: #0000FF"><strong>abort</strong></span>

EXEC usp_ShoppingCartEmpty @CartID;

<span style="color: #0000FF"><strong>COMMIT</strong></span> <span style="color: #0000FF"><strong>TRANSACTION</strong></span> AddOrder;
<span style="color: #0000FF"><strong>RETURN</strong></span> <span style="color: #4444FF">(</span>0<span style="color: #4444FF">)</span>;

<span style="color: #0000FF"><strong>abort</strong></span><span style="color: #4444FF">:</span>
ROLLBACK <span style="color: #0000FF"><strong>TRANSACTION</strong></span> AddOrder
<span style="color: #0000FF"><strong>RETURN</strong></span> <span style="color: #4444FF">(</span>@err<span style="color: #4444FF">)</span>;


</pre>
        <b>Database Tables Used: </b>
        <P>
            <i>SalesOrderHeader:</i>
        &nbsp;The SalesOrderHeader table contains the header information about all the orders in
        the system (i.e., all sales transactions that are actually completed. Users must check out
        their cart to place an order in the system). When an order is created, an entry
        is made in the SalesOrderHeader table with the CustomerID, OrderDate and ShipDate. Then,
        any information about the products in the order is added to the SaleOrderDetail
        table as discussed below. Note that if no OrderDate or ShipDate is provided,
        the current date is entered as default.
        <p>
            <i>SalesOrderDetail:</i>
        &nbsp;The SalesOrderDetail table contains detailed product information for each
        order in the system. For each product in an order, an entry is made into the
        SalesOrderDetail table containing the ProductID, Quantity and current UnitCost of
        the item. There is a many to one relationship between SalesOrderDetail table and
        the SalesOrderHeader table. Note the primary key in the table is both SalesOrderID and
        LineNumber (since those two columns are the minimum required to guarantee
        uniqueness of a record).
        <p>
            <IMG src="1x1.gif" width="25"> <IMG src="../docs/images/SalesOrderHeader-Detail.gif" width="585" height="455">
            
        <P><i>ShoppingCartItem:</i> &nbsp;&nbsp;The ShoppingCartItem table keeps track of the items a user has purchased.&nbsp;
        Its primary key is the ShoppingCartItemID field.&nbsp; The ShoppingCartID is a string which is used to
        identify the user who owns the basket of items.&nbsp; There is a many to one
        relationship between the ShoppingCartItem table and the Product table.&nbsp; Note that
        if not Quantity is supplied, a default of 1 is entered.

        <p>

        <img src="1x1.gif" width=25> <IMG src="../docs/images/ShoppingCart.gif" width="175" height="167"></p>
    </body>
</html>
